﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Zyg.SSO.Core.Business;
using Zyg.Utility.DBUtility;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class SqlProvider : DALProvider
    {
        public override Zyg.SSO.Core.Business.AccountApp SelectAccountApp(Guid id)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, id);
            SqlDataReader reader = db.ExecuteSPReader("usp_SelectAccountApp");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.AccountApp accountApp = new AccountApp();

                if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                reader.Close();

                return accountApp;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override AccountApp SelectAccountApp(Guid accountId, Guid appId)
        {
            SqlServerUtility db = new SqlServerUtility();

            string where = string.Format(" [AccountID] = '{0}' and [AppID] = '{1}'", accountId.ToString(), appId.ToString());

            db.AddParameter("@WhereCondition", SqlDbType.VarChar, where);
            SqlDataReader reader = db.ExecuteSPReader("usp_SelectAccountAppsDynamic");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.AccountApp accountApp = new AccountApp();

                if (!reader.IsDBNull(0)) accountApp.Id = reader.GetGuid(0);
                if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                accountApp.MarkOld();

                reader.Close();

                return accountApp;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, accountApp.Id);
            db.AddParameter("@AccountID", SqlDbType.UniqueIdentifier, accountApp.AccountID);
            db.AddParameter("@AppID", SqlDbType.UniqueIdentifier, accountApp.AppID);
            db.AddParameter("@DateCreated", SqlDbType.DateTime, accountApp.DateCreated);
            db.AddParameter("@ExpireTime", SqlDbType.DateTime, accountApp.ExpireTime);

            db.ExecuteSP("usp_InsertAccountApp");
        }

        public override void DeleteAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, accountApp.Id);

            db.ExecuteSP("usp_DeleteAccountApp");
        }

        public override void UpdateAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, accountApp.Id);
            db.AddParameter("@AccountID", SqlDbType.UniqueIdentifier, accountApp.AccountID);
            db.AddParameter("@AppID", SqlDbType.UniqueIdentifier, accountApp.AppID);
            db.AddParameter("@DateCreated", SqlDbType.DateTime, accountApp.DateCreated);
            db.AddParameter("@ExpireTime", SqlDbType.DateTime, accountApp.ExpireTime);

            db.ExecuteSP("usp_UpdateAccountApp");
        }

        public override IList<AccountApp> FillAccountApps(Account account, App app)
        {
            IList<AccountApp> results = new List<AccountApp>();

            SqlServerUtility db = new SqlServerUtility();
            SqlDataReader reader;

            if (account != null && app == null)
            {
                db.AddParameter("@AccountID", SqlDbType.UniqueIdentifier, account.Id);
                reader = db.ExecuteSPReader("usp_SelectAccountAppsByAccountID");
            }
            else if (account == null && app != null)
            {
                db.AddParameter("@AppID", SqlDbType.UniqueIdentifier, app.Id);
                reader = db.ExecuteSPReader("usp_SelectAccountAppsByAppID");
            }
            else if (account != null && app != null)
            {
                string where = string.Format(" [AccountID] = '{0}' and [AppID] = '{1}'", account.Id.ToString(), app.Id.ToString());

                db.AddParameter("@WhereCondition", SqlDbType.VarChar, where);
                reader = db.ExecuteSPReader("usp_SelectAccountAppsDynamic");
            }
            else
            {
                reader = null;
            }

            if (reader != null && !reader.IsClosed)
            {
                while (reader.Read())
                {
                    AccountApp accountApp = new AccountApp();

                    if (!reader.IsDBNull(0)) accountApp.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                    if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                    accountApp.MarkOld();

                    results.Add(accountApp);
                }

                reader.Close();
            }

            return results;
        }

        public override void DeleteAccountApps(Account account)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@AccountID", SqlDbType.UniqueIdentifier, account.Id.ToString());

            db.ExecuteSP("usp_DeleteAccountAppsByAccountID");
        }

        public override void DeleteAccountApps(App app)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@AppID", SqlDbType.UniqueIdentifier, app.Id.ToString());

            db.ExecuteSP("usp_DeleteAccountAppsByAppID");
        }

        public override void DeleteUpdateAccountApps(Account account, IList<AccountApp> newRelations)
        {
            DeleteUpdateAccountApps(account, null, newRelations);
        }

        public override void DeleteUpdateAccountApps(App app, IList<AccountApp> newRelations)
        {
            DeleteUpdateAccountApps(null, app, newRelations);
        }

        public override void DeleteUpdateAccountApps(Account account, App app, IList<AccountApp> newRelations)
        {
            SqlServerUtility db = new SqlServerUtility();

            StringBuilder sql = new StringBuilder();

            if (account != null && app == null)
            {
                db.AddParameter("@AccountID", SqlDbType.UniqueIdentifier, account.Id);

                sql.Append("Delete From [AccountApp] Where [AccountID] = @AccountID; ");

                int i = 0;

                foreach (AccountApp accountApp in newRelations)
                {
                    db.AddParameter("@ID" + i, SqlDbType.UniqueIdentifier, accountApp.Id);
                    db.AddParameter("@AppID" + i, SqlDbType.UniqueIdentifier, accountApp.AppID);
                    db.AddParameter("@DateCreated" + i, SqlDbType.DateTime, accountApp.DateCreated);
                    db.AddParameter("@ExpireTime" + i, SqlDbType.DateTime, accountApp.ExpireTime);

                    //sql.Append(string.Format("If NOT EXISTS (SELECT NULL FROM [AccountApp] WHERE [AccountID] = @AccountID AND [AppID] = @AppID{0}", i));
                    //sql.Append(" Begin ");
                    sql.Append("Insert Into [AccountApp] ([ID], [AccountID], [AppID], [DateCreated],[ExpireTime]) VALUES ( ");
                    sql.Append(String.Format("@ID{0}, @AccountID, @AppID{0}, @DateCreated{0}, @ExpireTime{0})", i));
                    //sql.Append(" END ");
                    i++;
                }
            }
            else if (account == null && app != null)
            {
                db.AddParameter("@AppID", SqlDbType.UniqueIdentifier, app.Id);

                sql.Append("Delete From [AccountApp] Where [AppID] = @AppID; ");

                int i = 0;

                foreach (AccountApp accountApp in newRelations)
                {
                    db.AddParameter("@ID" + i, SqlDbType.UniqueIdentifier, accountApp.Id);
                    db.AddParameter("@AccountID" + i, SqlDbType.UniqueIdentifier, accountApp.AppID);
                    db.AddParameter("@DateCreated" + i, SqlDbType.DateTime, accountApp.DateCreated);
                    db.AddParameter("@ExpireTime" + i, SqlDbType.DateTime, accountApp.ExpireTime);

                    //sql.Append(string.Format("If NOT EXISTS (SELECT NULL FROM [AccountApp] WHERE [AccountID] = @AccountID{0} AND [AppID] = @AppID ", i));
                    //sql.Append(" Begin ");
                    sql.Append("Insert Into [AccountApp] ([ID], [AccountID], [AppID], [DateCreated],[ExpireTime]) VALUES ( ");
                    sql.Append(String.Format("@ID{0}, @AccountID{0}, @AppID, @DateCreated{0}, @ExpireTime{0});", i));
                    //sql.Append(" END ");
                    i++;
                }
            }
            else
            {
                // Do nothing.
            }

            try
            {
                db.AutoCloseConnection = false;
                db.Connect();
                db.BeginTransaction();
                //db.Transaction.IsolationLevel = IsolationLevel.

                db.ExecuteSql(sql.ToString());
                db.CommitTransaction();
            }
            catch
            {
                db.RollbackTransaction();
            }
            finally
            {
                db.Disconnect();
            }
        }
    }
}
